﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;


namespace Ylh.SQLServerDAL
{
    public class CustFollowDAL : ICustFollow
    {
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int InsertFlowInfo(string ConStr, Ylh.Model.TbCustFollow model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbCustFollow(");
            strSql.Append("CustID,StaffID,FollowType,OtherFollow,FollowTime,FollowSituation,intentions,Remark,NextFlow)");
            strSql.Append(" values (");
            strSql.Append("@CustID,@StaffID,@FollowType,@OtherFollow,@FollowTime,@FollowSituation,@intentions,@Remark,@NextFlow)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@FollowType", SqlDbType.VarChar,10),
					new SqlParameter("@OtherFollow", SqlDbType.VarChar,100),
					new SqlParameter("@FollowTime", SqlDbType.DateTime),
					new SqlParameter("@FollowSituation", SqlDbType.Text),
                     new SqlParameter("@intentions",SqlDbType.VarChar,100),
					new SqlParameter("@Remark",SqlDbType.VarChar,1000),
					new SqlParameter("@NextFlow", SqlDbType.DateTime)};
            parameters[0].Value = model.CustID;
            parameters[1].Value = model.StaffID;
            parameters[2].Value = model.FollowType;
            parameters[3].Value = model.OtherFollow;
            parameters[4].Value = model.FollowTime;
            parameters[5].Value = model.FollowSituation;
            parameters[6].Value = model.Intentions;
            parameters[7].Value = model.Remark;
            if (model.NextFlow == null)
            {
                parameters[8].Value = DBNull.Value;
            }
            else
            {
                parameters[8].Value = model.NextFlow;
            }
            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            return Convert.ToInt32(dt.Rows[0][0]);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateFlowInFo(string ConStr, Ylh.Model.TbCustFollow model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbCustFollow set ");
            strSql.Append("CustID=@CustID,");
            strSql.Append("StaffID=@StaffID,");
            strSql.Append("FollowType=@FollowType,");
            strSql.Append("OtherFollow=@OtherFollow,");
            strSql.Append("FollowTime=@FollowTime,");
            strSql.Append("FollowSituation=@FollowSituation,");
            strSql.Append("intentions=@intentions,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("NextFlow=@NextFlow ");
            strSql.Append(" where CustFollow=@CustFollow ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustFollow", SqlDbType.Int,4),
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@FollowType", SqlDbType.VarChar,10),
					new SqlParameter("@OtherFollow", SqlDbType.VarChar,100),
					new SqlParameter("@FollowTime", SqlDbType.DateTime),
					new SqlParameter("@FollowSituation", SqlDbType.Text),
                   new SqlParameter("@intentions", SqlDbType.VarChar,100),
					new SqlParameter("@Remark", SqlDbType.VarChar,1000),
					new SqlParameter("@NextFlow", SqlDbType.DateTime)};
            parameters[0].Value = model.CustFollow;
            parameters[1].Value = model.CustID;
            parameters[2].Value = model.StaffID;
            parameters[3].Value = model.FollowType;
            parameters[4].Value = model.OtherFollow;
            parameters[5].Value = model.FollowTime;
            parameters[6].Value = model.FollowSituation;

            parameters[7].Value = model.Intentions;
            parameters[8].Value = model.Remark;
            if (model.NextFlow == null)
            {
                parameters[9].Value = DBNull.Value;
            }
            else
            {
                parameters[9].Value = model.NextFlow;
            }



            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;
        }




        public DataTable GetFlowData(string ConStr, string CustID, string StaffID)
        {

            string sql = @"SELECT * FROM TbCustFollow where CustID=" + CustID + " AND StaffID=" + StaffID;

            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }



        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(string ConStr, int CustFollow)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete TbCustFollow ");
            strSql.Append(" where CustFollow=@CustFollow ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustFollow", SqlDbType.Int,4)};
            parameters[0].Value = CustFollow;

            int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Ylh.Model.TbCustFollow GetModel(string ConStr, int CustFollow)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 CustFollow,CustID,StaffID,FollowType,OtherFollow,FollowTime,FollowSituation,intentions,Remark,NextFlow from TbCustFollow ");
            strSql.Append(" where CustFollow=@CustFollow ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustFollow", SqlDbType.Int,4)};
            parameters[0].Value = CustFollow;

            Ylh.Model.TbCustFollow model = new Ylh.Model.TbCustFollow();

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["CustFollow"].ToString() != "")
                {
                    model.CustFollow = int.Parse(dt.Rows[0]["CustFollow"].ToString());
                }
                if (dt.Rows[0]["CustID"].ToString() != "")
                {
                    model.CustID = int.Parse(dt.Rows[0]["CustID"].ToString());
                }
                if (dt.Rows[0]["StaffID"].ToString() != "")
                {
                    model.StaffID = int.Parse(dt.Rows[0]["StaffID"].ToString());
                }
                model.FollowType = dt.Rows[0]["FollowType"].ToString();
                model.OtherFollow = dt.Rows[0]["OtherFollow"].ToString();
                if (dt.Rows[0]["FollowTime"].ToString() != "")
                {
                    model.FollowTime = DateTime.Parse(dt.Rows[0]["FollowTime"].ToString());
                }
                model.FollowSituation = dt.Rows[0]["FollowSituation"].ToString();

                model.Remark = dt.Rows[0]["Remark"] != null ? dt.Rows[0]["Remark"].ToString() : "";
                
                if (dt.Rows[0]["NextFlow"].ToString() != "")
                {
                    model.NextFlow = DateTime.Parse(dt.Rows[0]["NextFlow"].ToString());
                }

                model.Intentions = dt.Rows[0]["Intentions"].ToString();


                return model;
            }
            else
            {
                return null;
            }
        }



        /// <summary>
        /// 获取是否存在上门拜访的这种情况
        /// </summary>
        /// <param name="ConStr"></param>
        /// <param name="CustID">客户编号</param>
        /// <returns></returns>
        public bool GetModelByMetting(string ConStr,string CustID)
        {
            string sql = @"select CustFollow  FROM TbCustFollow WHERE CustID =" + CustID + " AND FollowType in( 'C 上门拜访','D 客户来访')";
           DataTable dt=SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
           if (dt.Rows.Count > 0) return true;
           return false;
        }







    }
}
